USE MASTER
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'EtownManage')
DROP DATABASE EtownManage
GO


CREATE DATABASE EtownManage
GO

USE EtownManage
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Company')
DROP TABLE Company
GO

CREATE TABLE Company
	(
	  companyId INT IDENTITY PRIMARY KEY,
	  name NVARCHAR(50),
	  taxCode INT,
	  business NVARCHAR(20),
	  [description] NVARCHAR(MAX),
	  create_date DATETIME NULL DEFAULT (getdate())
	)
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'GroupAccount')
DROP TABLE GroupAccount
GO


CREATE TABLE GroupAccount
	(
	  groupAccountId INT IDENTITY PRIMARY KEY,
	  name NVARCHAR(30),
	  [description] NVARCHAR(MAX),
      create_date DATETIME NULL DEFAULT (getdate())
	 )
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Account')
DROP TABLE Account
GO


CREATE TABLE Account
	(
	  accountId INT IDENTITY PRIMARY KEY,
	  companyId INT FOREIGN KEY REFERENCES Company(companyId) ON DELETE SET NULL ON UPDATE SET NULL,
	  loginName NVARCHAR(20)NOT NULL ,
	  [password] NVARCHAR(20),
	  firstName NVARCHAR(15),
	  lastName NVARCHAR(15),
	  position NVARCHAR(30),
	  dateOfBirth DATETIME,
	  sex INT,
	  phone NVARCHAR(12),
	  CHECK(phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
      OR phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
      [address] NVARCHAR(30),
      socialId NVARCHAR(9),
      email NVARCHAR(30),
      create_date DATETIME NULL DEFAULT (getdate())
	 )
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Block')
DROP TABLE Block
GO


CREATE TABLE Block
	(
	  blockId INT IDENTITY PRIMARY KEY,
	  blockName NVARCHAR(10),
	  [description] NVARCHAR(MAX),
	  create_date DATETIME NULL DEFAULT (getdate())
	 )
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Floor')
DROP TABLE Floor
GO


CREATE TABLE Floor
	(
	  floorId INT IDENTITY PRIMARY KEY,
	  floorName NVARCHAR(10),
	  blockId INT FOREIGN KEY REFERENCES Block(blockId) ON DELETE SET NULL ON UPDATE SET NULL,
	  [description] NVARCHAR(MAX),
	  create_date DATETIME NULL DEFAULT (getdate())
	 )
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Unit')
DROP TABLE Unit
GO


CREATE TABLE Unit
	(
	  unitId INT IDENTITY PRIMARY KEY,
	  unitName NVARCHAR(10),
	  floorId INT FOREIGN KEY REFERENCES Floor(floorId) ON DELETE SET NULL ON UPDATE SET NULL,
	  blockId INT,
	  cost INT,
	  [status] INT,
	  [description] NVARCHAR(MAX),
	  create_date DATETIME NULL DEFAULT (getdate())
	 )
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'ParkingManage')
DROP TABLE ParkingManage
GO


CREATE TABLE ParkingManage
	(
	  parkingAreaId INT IDENTITY PRIMARY KEY,
	  parkingAreaName NVARCHAR(20),
	  companyId INT FOREIGN KEY REFERENCES Company(companyId) ON DELETE SET NULL ON UPDATE SET NULL,
	 )
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'ServicesManage')
DROP TABLE ServicesManage
GO


CREATE TABLE ServicesManage
	(
	  servicesManageId INT IDENTITY PRIMARY KEY,
	  companyId INT FOREIGN KEY REFERENCES Company(companyId) ON DELETE SET NULL ON UPDATE SET NULL,
	  electricBill INT,
	  waterBill INT,
	  securityBill INT,
	  total INT
	 )
GO